#encoding: utf-8
load 'rds_api.rb'
load 'wechat_api.rb'
load 'tutem_utils.rb'

@rds = @rds_tt

#用来从指定格式名称的登记表中读取登记图腾之友的用户填入到图腾之友数据中, 并即时更新相关统计数据
#e.g    图腾之友加入登记表_20220222.xlsx"
#Usage: ruby update-tupal.rb 20220222 

#这里用于将旧表中的数据复制到新表中，并标志上已复制符号
def copy_tupal11_v1_to_v2

    sql = "select * from tutem_mall.eb_tupal where uid >0 and sync=0"
    sqls = []
    (queryRds(sql)).each do |q|
        uid = q['uid']
        reg_name = q['reg_name']
        status = q['status']
        reg_data = q['reg_data']
        next_tip = q['next_tip']
        add_time = q['add_time']
        reg_time = q['reg_time']
        current = Time.now.strftime('%Y-%m-%d %H:%M:%S')
        history = "#{current} 申请登记数据从内测表单升级到集成数据\n#{q['history']}"

        reg_data = JSON.parse q['reg_data']

        isql = "
            INSERT INTO `tutem_mall`.`eb_tupal_enroll` (
            `uid`, `program_id`, `name`, `status`,
            `description`, `payload`, `answers`, `summary`,
            `add_time`, `submit_time`, `close_time`, `comment`, `survey_id`,
            `history`) VALUES (
                #{uid}, 3, '#{@rds.escape reg_name}的TUPAL11登记申请', '#{status}',
                '本计划用于支持：\n1）计划在一年或更长时间内持续使用指定产品\n2）且愿意定期提交并公开使用随访报告的用户\n用户加入此计划后可定期获得额外的积分抵扣券',
                '#{@rds.escape reg_data.to_json}','','#{next_tip}',
                #{add_time}, #{reg_time}, 0, '','TUPAL11-A',
                '#{@rds.escape history}'
            );
        "
        sqls += [isql]
    end

    usql = "update tutem_mall.eb_tupal set sync = 1 where uid in (select uid from tutem_mall.eb_tupal_enroll where program_id = 3)"
    sqls += [usql]

    commitTrans sqls

end

# 用于将v1表中的status和history等同步给表v2
def sync_tupal11_v1_to_v2 uid = 0
    sql = "update tutem_mall.eb_tupal_enroll e, tutem_mall.eb_tupal t set e.status = t.status, e.summary = t.next_tip, e.history = t.history where e.uid = t.uid and t.uid > 0 and e.program_id = 3;"
    sql = "update tutem_mall.eb_tupal_enroll e, tutem_mall.eb_tupal t set e.status = t.status, e.summary = t.next_tip, e.history = t.history where e.uid = t.uid and t.uid > 0 and e.program_id = 3 and t.uid = #{uid};" if uid > 0
    queryRds sql
end

def read_register_xlsx xlsx

    recs = []

    s = SimpleSpreadsheet::Workbook.read xlsx
    s.selected_sheet = s.sheets.first
    line_idx = 0
    updated_count = 0
    s.first_row.upto(s.last_row) do |line|

        rec = {}

        line_idx += 1
        next if line_idx == 1

        reg_name = (s.cell(line,9)).to_s
        reg_phone = (s.cell(line, 10)).to_s
        reg_at = s.cell(line, 3)
        reg_at = reg_at.strftime("%Y-%m-%d %H:%M:%S") if reg_at.class == DateTime #一般情况导出是字符串

        rec = {
            "idx" => line_idx,
            "nickname"=>s.cell(line,1),
            "reg_at"=>reg_at,
            "reg_name"=>reg_name,
            "reg_phone"=>reg_phone,
            "using"=>s.cell(line,6),
            "willing"=>s.cell(line,7),
            "to_choice"=>s.cell(line,11),
            "to_buy"=>s.cell(line,12),
            "source"=>xlsx,
        }

        recs += [rec]

    end

    return recs
end

def match_uid_to_recs recs
    new_recs = []
    recs.each do |rec|
        sql = ""
        if rec['reg_phone'] != ''
            sql = "select * from tutem_mall.eb_user where phone = '#{rec['reg_phone']}'"
        else
            sql = "select * from tutem_mall.eb_user where real_name = '#{rec['nickname']}' or nickname = '#{rec['nickname']}'"
        end
        uid = 0
        uids = []
        real_name = '---'
        nickname = '---'
        phone = '---'
        norec = true
        (queryRds(sql)).each do |r|
            norec = false
            uid = r['uid']
            uids += [uid]
            real_name = r['real_name']
            nickname = r['nickname']
            phone = r['phone']
            #puts "#{rec['idx']} #{rec['nickname']} #{rec['reg_name']} #{rec['reg_phone']} : #{uid} #{real_name} #{nickname} #{phone}"
            break
        end
        #puts ">>#{rec['idx']} #{rec['nickname']} #{rec['reg_name']} #{rec['reg_phone']} : #{uid} #{real_name} #{nickname} #{phone}" if norec
        rec.store "uid", uid
        rec.store "real_name", real_name
        rec.store "nickname", nickname
        new_recs += [rec]
    end
    return new_recs
end

def reset_tupal_db
    puts "reset tupal db..."
    sqls = [ "delete from tutem_mall.eb_tupal where 1=1;", "alter table tutem_mall.eb_tupal auto_increment = 1;" ]
    commitTrans sqls
    puts "done\n"
end

def register_tupal_db xlsx

    puts "register tupal application form to db..."
    recs = match_uid_to_recs ( read_register_xlsx xlsx )
    sqls = []

    recs.each do |rec|
        uid = rec['uid']
        reg_name = rec['reg_name']
        reg_phone = rec['reg_phone']
        reg_time = (Time.parse(rec['reg_at'])).to_i
        reg_data = rec.to_json
        source = rec['source']

        #先看是否已存在于数据库中
        no_rec = true
        sql = "select id, uid, reg_phone from tutem_mall.eb_tupal where  ( uid != 0 and  uid = #{uid} ) or reg_phone = '#{reg_phone}'"
        (queryRds(sql)).each do |q|
            #puts "uid#{uid} already existed! in eb_tupal##{q['id']}"
            no_rec = false
        end

        #如果未存在于数据库中则添加纪录
        if no_rec
            print '*'
            sql = "insert into tutem_mall.eb_tupal (
                uid, reg_name, reg_phone, reg_time,reg_data,
                status,next_tip,add_time,update_time,source
          ) values (
                #{uid}, '#{@rds.escape reg_name}', '#{reg_phone}', #{reg_time}, '#{@rds.escape reg_data}',
                'SUBMITTED','已登记，等待审核',#{Time.now.to_i},#{Time.now.to_i},'#{@rds.escape source}'
          );"
            sqls += [sql]
        else
            print '.'
        end

    end

    commitTrans sqls

    #update history for new recs
    sql = "update tutem_mall.eb_tupal set history = concat( concat( from_unixtime(reg_time), ' 用户填写图腾之友登记表\\n'), concat(from_unixtime(add_time),' 管理员从登记表导入系统\\n') ) where history is NULL"
    queryRds sql

    puts "done\n"

end

#将未存在于tutem_mall.eb_tupal中，已经新登记于tutem_mall.eb_tupal_enroll中program_id==3的新登记数据注册到旧表中
def tupal11_v2_to_v1_sqls

    sqls = []
    sql = "select * from tutem_mall.eb_tupal_enroll where program_id = 3 and uid not in (select uid from tutem_mall.eb_tupal) and status != 'DRAFT'"
    (queryRds(sql)).each do |rec|

        payload = JSON.parse rec['payload']
        uid = rec['uid']
        status = rec['status']
        reg_name = payload['answer'][0]['questions'][0]['text']
        reg_phone = payload['answer'][0]['questions'][12]['text']
        reg_time = (Time.parse(payload['ended_at'])).to_i
        reg_data = payload.to_json
        source = "wj.qq.com_survey_id_#{payload['survey_id']}_answer_id_#{payload['answer_id']}"
        history = rec['history']
        summary = rec['summary']

        print '*'
        sql = "insert into tutem_mall.eb_tupal (
                    uid, reg_name, reg_phone, reg_time,reg_data,
                    status,next_tip,add_time,update_time,source,sync
                ) values (
                    #{uid}, '#{@rds.escape reg_name}', '#{reg_phone}', #{reg_time}, '#{@rds.escape reg_data}',
                    '#{status}','#{summary}',#{Time.now.to_i},#{Time.now.to_i},'#{@rds.escape source}',1
                );"
        sqls += [sql]

    end

    return sqls

    puts "done\n"

end

def update_latest_lecture
    puts "update latest B3 lecture..."
    usqls = []
    sql = "select uid, count(*) as b3_times from tutem_mall.eb_user_bill where uid in (select uid from tutem_mall.eb_tupal where uid !=0) and category = 'integral' and title like '%-B3%' group by uid"
    (queryRds(sql)).each do |q|
        usql = "update tutem_mall.eb_tupal set b3_times = #{q['b3_times']}, update_time = #{Time.now.to_i} where uid = #{q['uid']}"
        usqls += [usql]
    end
    commitTrans usqls
    puts "done\n"
end

def update_mp_followed
    puts "update mp_followed..."
    usqls = []
    sql = "select t.id, t.uid, w.subscribe from tutem_mall.eb_tupal t left join tutem_mall.eb_wechat_user w on (t.uid = w.uid) where t.uid !=0"
    (queryRds(sql)).each do |q|
        subscribed = 0
        subscribed = q['subscribe'] if q['subscribe'] #因为uid可能是一个不存在的记录
        usql = "update tutem_mall.eb_tupal set mp_followed = #{subscribed}, update_time = #{Time.now.to_i} where id = #{q['id']};"
        usqls += [usql]
        print '.'
    end
    commitTrans usqls
    puts "done\n"
end

def update_wu_added
    puts "update wu_added..."
    usqls = []
    sql = "select id, uid from tutem_mall.eb_tupal where uid != 0"
    (queryRds(sql)).each do |q|
        usql = "UPDATE tutem_mall.eb_tupal
      SET update_time = #{Time.now.to_i},
          wu_added = (
          SELECT
              count(*) AS wu_added 
          FROM
              tutem_mall.eb_wework_external_contact 
          WHERE
          external_userid IN ( SELECT external_userid FROM tutem_mall.eb_wechat_user WHERE uid != 0 AND uid = #{q['uid']} )) 
      WHERE
          uid = #{q['uid']}; "
        usqls += [usql]
        print '.'
    end
    commitTrans usqls
    puts "done\n"
end

def update_cg_joined
    puts "update cg_joined..."
    usqls = []
    sql = "select id, uid from tutem_mall.eb_tupal where uid != 0"
    (queryRds(sql)).each do |q|
        usql = "UPDATE tutem_mall.eb_tupal
      SET update_time = #{Time.now.to_i},
          cg_joined = (
          SELECT
              count(*) AS cg_joined 
          FROM
              tutem_mall.eb_wework_chat_member
          WHERE
          userid IN ( SELECT external_userid FROM tutem_mall.eb_wechat_user WHERE uid != 0 AND uid = #{q['uid']} )) 
      WHERE
          uid = #{q['uid']}; "
        usqls += [usql]
        print '.'
    end
    commitTrans usqls
    puts "done\n"
end

def update_wa_registered
    puts "update wa_registered..."
    usqls = []
    sql = "select id, uid from tutem_mall.eb_tupal where uid != 0"
    (queryRds(sql)).each do |q|
        usql = "
      UPDATE tutem_mall.eb_tupal 
      SET update_time = #{Time.now.to_i},
          wa_registered = (
          SELECT
              count(*) AS wa_registered 
          FROM
              tutem_mall.eb_user
          WHERE
          uid = #{q['uid']} and phone like '1%' )
      WHERE
          uid = #{q['uid']}; "
        usqls += [usql]
        print '.'
    end
    commitTrans usqls
    puts "done\n"
end

#检查特定uid在计划登记前或登记后的购买有效盒数, 当为零时处理所有有效uid
def qualify_tupal11_goods token, target_uid = 0, not_drill = false

    and_this_uid = ""
    and_this_uid = " and uid = #{target_uid}" if target_uid > 0

    puts "qualify carts #{token} retime for user..."
    return if token != 'before' && token != 'since'

    #重置历史统计数据，因为如果手工修改了订单支付时间，会导致部分数据不能更新
    usql = "update tutem_mall.eb_tupal set update_time = #{Time.now.to_i}, #{token=='before'?'before_count':'since_count'} = 0, #{token=='before'?'before_paid':'since_paid'} = 0.00 where uid != 0 #{and_this_uid}"
    queryRds usql

    users = {}
    sql = "select uid, reg_time from tutem_mall.eb_tupal where uid != 0 #{and_this_uid}"# and status = 'REGISTERED'"
    (queryRds(sql)).each { |q| users.store q['uid'], q['reg_time'] }

    usqls = []
    users.each do |uid, reg_time|

        print '*'

        #eb_store_order uid = #{uid} 只找该用户
        #eb_store_order paid = 1 保证只对已付款订单处理
        #eb_store_order status = 2 or status = 3 保证只包括"已收货"及"已评价"的订单
        #eb_store_order refund_status = 0 保证不包括"想退款"及"已退款的订单"的订单
        #eb_store_order pay_type = 'weixin' 保证只对使用微信支付的订单做统计

        exception_coupons = 'and coupon_id not in (21, 22, 23, 24, 25)' #参加了真研计划的部分不适用
        exception_orders = ' and id not in (19105)' #特定用户9531订单522订单不返回；
        qualified_pcodes = ['TTYX','TTYS','TTYMJ','TTYMB']
        define_pay_time = "pay_time #{token=='before'?'<':'>='} #{reg_time}"

        #本来是20.01用于首单优惠券返现抵扣的，因为直接操作退款过一次
        select_order_ids = "select id from tutem_mall.eb_store_order
            where uid = #{uid} and paid = 1 and ( status = 2 or status = 3) and refund_status = 0 and refunded_amount <= 137.01 #{exception_coupons} #{exception_orders} and pay_type = 'weixin' and #{define_pay_time}"
        define_pcodes = "(b.pcode_info like '%TTYS%' or b.pcode_info like '%TTYX%' or b.pcode_info like '%TTYMJ%' or b.pcode_info like '%TTYMB%')"
        sql = "select * from tutem_mall.eb_pickup_book b left join tutem_mall.eb_store_order o on (b.oid = o.id) where b.oid in ( #{select_order_ids} ) and #{define_pcodes} and b.pay_type = 'weixin' and b.refunded_amount <= 137.01 and b.uid = #{uid}"

        qualified_qty = 0
        qualified_paid = 0.00
        (queryRds(sql)).each do |q|
            uid = q['uid']
            goods_info = JSON.parse q['goods_info']
            order_id = q['oid']
            goods_info.each do |goods|
                if goods['weixin_paid'] > 0 && ( qualified_pcodes.include? goods['pcode'] )
                    qualified_qty = qualified_qty + goods['standard_qty']
                    #去掉活动商品 只能用排除法，因为老订单那会儿没有生成barcode
                    #未来所有的促销组合可以使用"xxxx-nn-code"例如TTYX-1-230618的形式生成
                    if !(
                        goods['attr_barcode'].include?('001-09') ||
                        goods['attr_barcode'].include?('001-18') ||
                        goods['attr_barcode'].include?('-230618') #||
                        #goods['attr_barcode'].include?('-231111') #这个节日在15日的时候无意间被漏掉跳过了
                    )
                        qualified_paid = qualified_paid + goods['weixin_paid']
                    else
                        puts "1111" if goods['attr_barcode'].include?('-231111')
                    end
                end
            end
            qualified_paid = qualified_paid - q['refunded_amount']
        end

        if qualified_qty > 0
            usql = "update tutem_mall.eb_tupal set update_time = #{Time.now.to_i}, #{token=='before'?'before_count':'since_count'} = #{qualified_qty}, #{token=='before'?'before_paid':'since_paid'} = #{qualified_paid.to_f.round(2)} where uid = #{uid}"
            usqls += [usql]
        end

    end

    if not_drill
        commitTrans usqls
    else
        ap usqls
    end
    puts "done\n"

end

#当not_drill为真的时候，会实际更新eb_tupal数据库及eb_tupal_enroll中对应program_id==3的数据，并推送給用户和企业微信小结
#否则的话只会在控制台打印小结信息
def review_tupals target_uid = 0, not_drill = false

    and_this_uid = ""
    and_this_uid = " and uid = #{target_uid}" if target_uid > 0

    usqls = []
    recs = {} #用于根据用户uid储存模板发送信息

    ww_notices = {}

    uid_array_to_notify = []

    program_status={}
    program_status.store 'SUBMITTED', '用户已提交登记'
    program_status.store 'PENDING',   '需完善暂未批准'
    program_status.store 'TRIAL',     '进入计划观察期'
    program_status.store 'MEMBER',    '进入正式计划期'
    program_status.store 'APPROVED',  '申请已获得批准' #这个是不用的

    puts "review enrolled tupal users..."
    sql = "select * from tutem_mall.eb_tupal where status != 'DISABLED' #{and_this_uid}"
    (queryRds(sql)).each do |q|

        id = q['id']
        uid = q['uid']
        reg_data = q['reg_data']
        json = JSON.parse reg_data
        nickname = json['nickname']
        reg_name = q['reg_name'].to_s
        reg_phone = q['reg_phone'].to_s
        reg_at = Time.at(q['reg_time']).strftime('%Y-%m-%d %H:%M:%S')
        reg_date = Time.at(q['reg_time']).strftime('%Y-%m-%d')
        next_tip = q['next_tip']
        mp_followed = q['mp_followed']
        wa_registered = q['wa_registered']
        wu_added = q['wu_added']
        cg_joined = q['cg_joined']
        b3_times = q['b3_times']
        next_tip = q['next_tip']
        status = q['status']
        before_count = q['before_count']
        trial_count = q['trial_count']
        since_count = q['since_count']
        member_count = q['member_count']
        history = q['history'].to_s

        if  uid == 0 || mp_followed == 0 || wa_registered == 0 #|| b3_times == 0 #|| cg_joined == 0
            status = 'PENDING'
            next_tip = "未通过审核，请完成以下内容："
            next_tip += "\n□至少使用一次小程序" if uid == 0
            next_tip += "\n□在小程序完善注册信息" if wa_registered == 0
            next_tip += "\n□关注图腾生命医学公号" if mp_followed == 0
            #next_tip += "\n□添加小图企业微信" if wu_added == 0
            #next_tip += "\n□加入企业微信群" if cg_joined == 0
            #next_tip += "\n□至少签到一次计划讲座" if b3_times == 0
        else
            status = 'TRIAL'  # if before_count < 9 and trial_count < 9
            status = 'MEMBER' if before_count >= 9 || trial_count >= 9 
            next_tip = "通过审核进入正式期，登记日期#{reg_date}" if status == 'MEMBER'
            next_tip = "通过审核进入观察期，登记日期#{reg_date}" if status == 'TRIAL'
            #next_tip = "已通过审核"
        end

        #准备通知审核信息
        rec = {}

        if status != q['status']
            print '*'
            #准备状态更新SQL
            moment = Time.now.strftime('%Y-%m-%d %H:%M:%S')
            prev_status = program_status[q['status']].to_s
            curr_status = program_status[status].to_s
            history = "#{moment} 经审核由【#{prev_status}】变更为【#{curr_status}】\n" + q['history'].to_s #in case text is null
            usql = "update tutem_mall.eb_tupal set status = '#{@rds.escape status}', next_tip = '#{@rds.escape next_tip}', history = '#{@rds.escape history}' where id = #{id}"
            usqls += [usql]
            usql = "update tutem_mall.eb_tupal_enroll set status = '#{@rds.escape status}', summary = '#{@rds.escape next_tip}', history = '#{@rds.escape history}' where uid = #{uid} and program_id = 3"
            usqls += [usql]
            #准备模板消息
            isql = "select openid, subscribe from tutem_mall.eb_wechat_user where uid = #{uid}"
            subscribe = 0
            (queryRds(isql)).each do |i|
                openid = i['openid'].to_s
                subscribe = i['subscribe']
                if !openid.empty?
                    template_id = 'EjDB1-4Jh0_ROs5-CVghAAg7SxA25n3GEWeoVqRct0E'
                    url = 'https://www.totembio.cn/'
                    miniprogram = {appid: "wx1b441d1a37b278e1", pagepath:  "pages/user/user" }
                    first = "\n#{nickname}您登记的TUPAL11图腾之友长期用户支持计划有新的反馈"
                    keyword1 =  reg_at
                    keyword2 = '系统审核计划资格评估'
                    keyword3 = next_tip
                    remark = '图腾之友长期用户支持计划每月19号前结算积分补贴，详情请留意图腾生命医学公众号模板消息'
                    rec = {
                        touser: openid, template_id: template_id, url: url, miniprogram: miniprogram,
                        data: {
                            first:  { value:  "#{first}"},                            #抬头,这一行加了颜色也不起作用
                            keyword1:  { value:  "#{keyword1}", color:  '#888888' },  #反馈时间
                            keyword2:  { value:  "#{keyword2}", color:  '#173177' },  #反馈内容
                            keyword3:  { value:  "#{keyword3}", color:  '#88b04b' },  #回复内容
                            remark:  { value: "#{remark}", color:  '#888888' },       #备注
                        },
                    }
                end
            end
            #将该uid记为待推送uid
            uid_array_to_notify += [uid]
            #puts "\nuid:#{uid} old:#{q['status']} now:#{status}"
        else
            print '.'
        end

        existed_notice = ww_notices[status].to_s
        ww_notice_body = existed_notice + "**[#{sprintf('%02d',id)}] #{nickname} [#{uid==0?'----':sprintf('%04d',uid)}] #{subscribe==0?'x':''}**\n"
        ww_notice_body += " #{reg_phone} #{reg_name} (#{before_count},#{since_count},#{trial_count+member_count})\n"
        ww_notice_body += " #{next_tip}\n"
        ww_notices.store status, ww_notice_body

        recs.store uid, rec if !rec.empty?

    end

    if not_drill
        commitTrans usqls
    else
        ap usqls
    end
    puts 'done'

    puts "sending template message to users..."
    recs.each do |uid, rec|
        #只对状态有变化的用户发送通知
        if uid_array_to_notify.include? uid
            if not_drill
                print '.'
                wechat_post :sendTemplateMessage, rec
            else
                puts "To U##{uid}: #{rec['first']}"
            end
        end
    end
    puts 'done'

    puts "sending notice to wework..."
    ww_notices.each do |key, body|
        title = '处理汇总'
        title = '已提申请' if key == 'SUBMITTED'
        title = '正式成员' if key == 'MEMBER'
        title = '试用观察' if key == 'TRIAL'
        title = '尚未批准' if key == 'PENDING'
        ww_notice = "# 图腾之友登记 #{title}\n"
        ww_notice += "#{key} #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n\n"
        ww_notice += body
        ww_notice += "> x：不能发消息，需电话通知\n"
        ww_notice += "> (a,b,c)：计划前、后、已发放\n"
        if not_drill
            print '.'
            send_message_to_bot 'WWBOT_XT', ww_notice, ['@all']
        else
            puts ww_notice
        end
    end
    puts "done"

end

def reset_history
    #sql = "update tutem_mall.eb_tupal set history = concat( concat( from_unixtime(reg_time), ' 用户填写图腾之友登记表\\n'), concat(from_unixtime(add_time),' 管理员从登记表导入系统\\n') ) where history is NULL"
    sql = "update tutem_mall.eb_tupal set status = 'SUBMITTED', history = concat( concat( from_unixtime(reg_time), ' 用户填写图腾之友登记表\\n'), concat(from_unixtime(add_time),' 管理员从登记表导入系统\\n') ) "
    queryRds sql
end

def adjust_reg_time_by_uid uid, new_date

    new_time = Time.parse(new_date).to_i
    sql = "select id, reg_time, next_tip from tutem_mall.eb_tupal where uid = #{uid}"
    usqls = []
    (queryRds(sql)).each do |q|
        old_time = q['reg_time']
        old_time_str = Time.at(old_time).strftime('%Y-%m-%d %H:%M:%S')
        new_time_str = Time.at(new_time).strftime('%Y-%m-%d %H:%M:%S')
        new_date_str = Time.at(new_time).strftime('%Y-%m-%d')
        now_time_str = Time.now.strftime('%Y-%m-%d %H:%M:%S')
        message = "[#{now_time_str}] OMS将登记日期从#{old_time_str}改为#{new_time_str}\n"
        next_tip = q['next_tip']
        next_tip = next_tip + '>>>' + new_date_str if next_tip.include? '登记日期'
        usqls += ["update tutem_mall.eb_tupal set reg_time = #{new_time}, next_tip = '#{next_tip}', history = concat('#{@rds.escape message}',history) where id = #{q['id']}"]
        usqls += ["update tutem_mall.eb_tupal_enroll set submit_time = #{new_time}, history = concat('#{@rds.escape message}', history) where program_id = 3 and uid = #{uid}"] #其他的使用
    end
    commitTrans usqls

end

def update_tupal_flags
    update_latest_lecture
    update_wu_added
    update_wa_registered
    update_cg_joined
    update_mp_followed
end

def update_tupal_for_uid uid, not_drill=false

    update_tupal_flags

    qualify_tupal11_goods 'before', uid, not_drill
    qualify_tupal11_goods 'since', uid, not_drill

    review_tupals uid, not_drill
    sync_tupal11_v1_to_v2 uid

end

###reset_tupal_db

# 调整个别用户
=begin

adjust_reg_time_by_uid 7144, '2022-09-07'
update_tupal_for_uid 7144, true

=end

# 批量处理所有用户

#current: 2023-06-07 14:49
#current: 2023-07-09 14:49
#current: 2023-08-09 11:12
#current: 2023-09-12 21:21
#current: 2023-10-10 21:21
#current: 2023-11-15 07:40
#current: 2023-12-21 23:11
#current: 2024-01-14 06:59
#current: 2024-02-19 06:59
#current: 2024-03-21 15:21
#current: 2024-04-25 09:50
#current :2024-06-11 12:43
#current: 2024-06-26 15:00
=begin
commitTrans tupal11_v2_to_v1_sqls
update_tupal_for_uid 0, true #更新基数且推送消息
=end
